package com.ccjq.jq167.student.dao.impl;

import com.ccjq.jq167.student.dao.StudentDao;
import com.ccjq.jq167.student.domain.StudentPo;
import com.ccjq.jq167.student.utils.DbConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    Connection con=null;
    PreparedStatement pre=null;
    ResultSet res=null;

    //查询个人
    List<StudentPo> list1=new ArrayList<StudentPo>();
    public List<StudentPo> selectInfo(String name) {
        con= DbConnection.getConnection();
        String sql="select * from student where name like '%"+name+"%'";
        try {
            pre=con.prepareStatement(sql);
            res=pre.executeQuery();
            while (res.next()){
                StudentPo studentPo1=new StudentPo();
                studentPo1.setId(res.getString("id"));
                studentPo1.setName(res.getString("name"));
                studentPo1.setAge(res.getInt("age"));
                studentPo1.setSex(res.getString("sex"));
                studentPo1.setAddress(res.getString("address"));
                studentPo1.setSchool(res.getString("school"));
                list1.add(studentPo1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(res,pre,con);
        }
        return list1;
    }


    //通过id查个人
    public StudentPo getInfo(String id) {
        con=DbConnection.getConnection();
        String sql="select * from student where id ='"+ id+"'";
        StudentPo student=new StudentPo();
        try {
            pre=con.prepareStatement(sql);
            res=pre.executeQuery();
            if (res.next()){
                student.setId(id);
                student.setName(res.getString("name"));
                student.setAge(res.getInt("age"));
                student.setSex(res.getString("sex"));
                student.setAddress(res.getString("address"));
                student.setSchool(res.getString("school"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            DbConnection.close(res,pre,con);
        }
        return student;
    }

    //添加
    public boolean insert(StudentPo studentPo) {
        boolean b=false;
        con=DbConnection.getConnection();
        String sql="insert into student values(?,?,?,?,?,?)";
        try {
            pre=con.prepareStatement(sql);
            pre.setString(1,studentPo.getId());
            pre.setString(2,studentPo.getName());
            pre.setInt(3,studentPo.getAge());
            pre.setString(4,studentPo.getSex());
            pre.setString(5,studentPo.getAddress());
            pre.setString(6,studentPo.getSchool());
            int i=pre.executeUpdate();
            if (i>0){
                b=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(res,pre,con);
        }
        return b;
    }

    //删除
    public boolean delete(StudentPo studentPo) {
        boolean b=false;
        con=DbConnection.getConnection();
        String sql="delete from student where id=?";
        try {
            pre=con.prepareStatement(sql);
            pre.setString(1,studentPo.getId());
            int i=pre.executeUpdate();
            if (i>0){
                b=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(res,pre,con);
        }
        return b;
    }

    //修改
    public boolean update(StudentPo studentPo) {
        boolean b=false;
        con=DbConnection.getConnection();
        String sql="update student set name=?,age=?,sex=?,address=?,school=? where id=?";
        try {
            pre=con.prepareStatement(sql);
            pre.setString(1,studentPo.getName());
            pre.setInt(2,studentPo.getAge());
            pre.setString(3,studentPo.getSex());
            pre.setString(4,studentPo.getAddress());
            pre.setString(5,studentPo.getSchool());
            pre.setString(6,studentPo.getId());
            int i=pre.executeUpdate();
            if (i>0){
                b=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(res,pre,con);
        }
        return b;
    }
}
